package com.bright.hxj.cwduijie.service;

import cn.hutool.json.JSONUtil;
import com.bright.ghj.common.util.PageUtil;
import com.bright.ghj.common.util.StringUtil;
import com.bright.hxj.cwduijie.manager.DataManager;
import com.bright.hxj.cwduijie.pojo.command.DuijieCommand;
import com.bright.hxj.cwduijie.pojo.dto.PageDTO;
import com.bright.hxj.cwduijie.pojo.po.DuijieAsync;
import com.bright.hxj.cwduijie.pojo.query.DuijieBaseDataQuery;
import com.bright.hxj.cwduijie.pojo.query.DuijieStatisticsQuery;
import com.bright.hxj.cwduijie.pojo.query.TreeStatisticsQuery;
import com.bright.hxj.cwduijie.pojo.vo.StatisticsDataVO;
import com.bright.hxj.cwduijie.pojo.vo.StatisticsMonthsDataVO;
import com.bright.hxj.cwduijie.repository.DuijieAsyncRepository;
import com.bright.hxj.cwduijie.repository.DuijieBatchRepository;
import com.bright.hxj.cwduijie.util.CommonUtil;
import com.bright.hxj.cwduijie.util.EntityManagerUtil;
import com.bright.ghj.common.util.ListUtil;
import com.bright.ghj.common.util.excel.ExcelUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.Predicate;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.YearMonth;
import java.util.*;

/**
 * @Author hxj
 * @Author Tz
 * @Date 2024/8/9 14:53
 * @Description 统计页面相关接口
 */

@Component
@RequiredArgsConstructor
@Slf4j
public class StatisticsService {

    private final ApiServiceFactory factory;
    private final DataManager dataManager;
    private final EntityManager entityManager;

    private final DuijieAsyncRepository duijieAsyncRepository;
    private final DuijieBatchRepository duijieBatchRepository;

    /**
     * 查询基础数据
     *
     * @param duijieQuery
     * @return
     */
    public Object getBaseDuijieData(DuijieBaseDataQuery duijieQuery) {
        String serviceCode = duijieQuery.getServiceCode();
        List<String> list = Collections.singletonList(serviceCode);

//        DuijieCommand duijieCommand = new DuijieCommand();
//        duijieCommand.setServiceCodes(list);

        List<AbstractApiService<?>> needDuijieApis = factory.getApisByServiceCode(list);

        Pageable pageable = PageUtil.toPageable(duijieQuery.getCurrent() - 1, duijieQuery.getSize(), null);
        for (AbstractApiService<?> needDuijieApi : needDuijieApis) {
            Page data = needDuijieApi.getJpaSpecificationExecutor().findAll(((root, criteriaQuery, criteriaBuilder) -> {

                List<Predicate> ps = new ArrayList<>();

                if (duijieQuery.getOpType() != null) {
                    ps.add(criteriaBuilder.equal(root.get("opType"), duijieQuery.getOpType()));
                }

                if (StringUtil.isNotBlank(duijieQuery.getCreditCode())) {
                    // v1.5.10 有些表没有统一社会信用代码字段 改成like ZT_DATA_KEY
                    if (needDuijieApi.isExistsTYSHXYDMField()) {
                        ps.add(criteriaBuilder.equal(root.get("TYSHXYDM"), duijieQuery.getCreditCode()));
                    } else {
                        ps.add(criteriaBuilder.like(root.get("ZT_DATA_KEY"), "%" + duijieQuery.getCreditCode() + "%"));
                    }
                }

                if (duijieQuery.getSjjhbz() != null) {
                    ps.add(criteriaBuilder.equal(root.get("sjjhbz"), duijieQuery.getSjjhbz()));
                }

                if (duijieQuery.getAccSet() != null) {
                    ps.add(criteriaBuilder.equal(root.get("accSet"), duijieQuery.getAccSet()));
                }

                String yearMonthColumnName = needDuijieApi.getYearMonthColumnName();
                if (duijieQuery.getYears() != null && duijieQuery.getMonths() != null && StringUtil.isNotBlank(yearMonthColumnName)) {
                    String selectedYearMonth = CommonUtil.getSelectedYearMonth(duijieQuery.getYears(), duijieQuery.getMonths());

                    ps.add(criteriaBuilder.equal(root.get(yearMonthColumnName), selectedYearMonth));
                }


                criteriaQuery.where(ps.toArray(new Predicate[0]));

                criteriaQuery.orderBy(criteriaBuilder.asc(root.get("id")));
                return criteriaQuery.getRestriction();
            }), pageable);

            PageDTO pageDTO = PageDTO.of(data.getTotalElements(), data.getTotalPages(), data.getContent());
            String s = JSONUtil.toJsonStr(pageDTO);
            return s;
        }

        return null;
    }

    /**
     * 查询统计数据-月份
     *
     * @param duijieQuery
     * @return
     */
    public Object getStatisticsMonthsData(DuijieStatisticsQuery duijieQuery) {

        StringBuffer stringBuffer;
        // v1.6.4 达梦语法
        if (EntityManagerUtil.IS_DM) {
            stringBuffer = new StringBuffer("select * from table(fn_dj_statistic_months_dm(:zucode, :years, :months)) where 1 = 1 ");
        } else {
            stringBuffer = new StringBuffer("select * from fn_dj_statistic_months(:zucode, :years, :months) where 1 = 1 ");
        }
        //参数集合
        Map<String, Object> parameterMap = new HashMap<>(2);

        parameterMap.put("zucode", duijieQuery.getCreditCode());
        parameterMap.put("years", duijieQuery.getYears());
        parameterMap.put("months", duijieQuery.getMonths());

        if (StringUtil.isNotBlank(duijieQuery.getTypeName())) {
            //条件
            stringBuffer.append(" and typeName like :typeName ");
            parameterMap.put("typeName", duijieQuery.getTypeName() + "%");
        }

        if (StringUtil.isNotBlank(duijieQuery.getInfo())) {
            //条件
            stringBuffer.append(" and info = :info ");
            parameterMap.put("info", duijieQuery.getInfo());
        }


        //排序
        stringBuffer.append(" order by sorting, info desc");


        Query nativeQuery = entityManager.createNativeQuery(stringBuffer.toString());

        //设置参数
        for (String key : parameterMap.keySet()) {
            nativeQuery.setParameter(key, parameterMap.get(key));
        }

        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(StatisticsMonthsDataVO.class));
        return (List<StatisticsMonthsDataVO>) nativeQuery.getResultList();
    }


    /**
     * 查询统计数据
     *
     * @param duijieQuery
     * @return
     */
    public Object getStatisticsData(DuijieStatisticsQuery duijieQuery) {

        // v1.6.4 达梦语法
        String functionName = EntityManagerUtil.IS_DM ? "table(fn_dj_statistic_dm(:zucode))" : "fn_dj_statistic(:zucode)";

        StringBuffer stringBuffer = new StringBuffer("select left(\"对接数据类型\", CHARINDEX('DJ', \"对接数据类型\") -2) as \"typeName\", \n" +
                "\"待推送\"+\"排队等省平台接收\"+\"省平台已接收，等待国家平台校验结果等待中\"+\"推送成功，并国家平台数据检验无误\"+\"推送成功，但数据表格有误，需调整数据\" as \"info1\",\n" +
                "\"排队等省平台接收\"+\"省平台已接收，等待国家平台校验结果等待中\" + \"推送成功，并国家平台数据检验无误\" + \"推送成功，但数据表格有误，需调整数据\" as \"info2\", \n" +
                "\"推送成功，并国家平台数据检验无误\" as\"info3\" from " + functionName + " where 1 = 1\n");
        //参数集合
        Map<String, Object> parameterMap = new HashMap<>(2);

        parameterMap.put("zucode", duijieQuery.getCreditCode());

        if (StringUtil.isNotBlank(duijieQuery.getTypeName())) {
            //条件
            stringBuffer.append(" and \"对接数据类型\" like :typeName ");
            parameterMap.put("typeName", duijieQuery.getTypeName() + "%");
        }

        Query nativeQuery = entityManager.createNativeQuery(stringBuffer.toString());

        //设置参数
        for (String key : parameterMap.keySet()) {
            nativeQuery.setParameter(key, parameterMap.get(key));
        }

        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(StatisticsDataVO.class));
        return (List<StatisticsDataVO>) nativeQuery.getResultList();
    }


    /**
     * 获取未处理的失败数据
     * @param duijieQuery
     * @return
     */
    public PageDTO getFailDetailData(DuijieBaseDataQuery duijieQuery) {
        StringBuilder queryCountSQL = new StringBuilder("select COUNT(*) from DUIJIE_ASYNC where taskId in (\n");
        List<AbstractApiService<?>> needDuijieApis = factory.getAllDuijieApis();
        int count = 0;
        for (AbstractApiService<?> needDuijieApi : needDuijieApis) {
            String tableName = needDuijieApi.getDataApi().getTableName();
            if (count != 0) {
                queryCountSQL.append("union \n");
            }
            queryCountSQL.append("select taskId from ").append(tableName).append(" with(nolock) where sjjhbz=2 \n");
            count++;
        }

        queryCountSQL.append(") \n");
        Map<String, Object> params = new HashMap<>(2);
        if (StringUtil.isNotBlank(duijieQuery.getCreditCode())) {
            queryCountSQL.append(" and batchId like :creditCode");
            params.put("creditCode", "%" + duijieQuery.getCreditCode() + "%");
        }
        if (StringUtil.isNotBlank(duijieQuery.getServiceCode())) {
            queryCountSQL.append(" and methodNo=:methodNo");
            params.put("methodNo", duijieQuery.getServiceCode());
        }

//        queryCountSQL.append(" order by createDate desc");

        Long total = EntityManagerUtil.queryForCount(entityManager, queryCountSQL.toString(), params);
        int pageCount = total.intValue() / duijieQuery.getSize();
        String querySQL = StringUtil.replace(queryCountSQL.toString(), "COUNT(*)", "id,taskId,type as \"type\",sjjhbz,writeLog,createDate,writeDate");
        querySQL += " order by createDate desc";
        List<Map<String, Object>> maps = EntityManagerUtil.queryForMapList(entityManager, querySQL, params, duijieQuery);
        return PageDTO.of(total, pageCount, maps);
    }

    public PageDTO getDuijieAsyncData(DuijieBaseDataQuery duijieQuery) {
        Pageable pageable = PageUtil.toPageable(duijieQuery.getCurrent() - 1, duijieQuery.getSize(), null);
        Page<DuijieAsync> data = duijieAsyncRepository.findAll(((root, criteriaQuery, criteriaBuilder) -> {

            List<Predicate> ps = new ArrayList<>();

            if (StringUtil.isNotBlank(duijieQuery.getCreditCode())) {
                ps.add(criteriaBuilder.like(root.get("batchId"), "%" + duijieQuery.getCreditCode() + "%"));
            }

            if (duijieQuery.getSjjhbz() != null) {
                ps.add(criteriaBuilder.equal(root.get("sjjhbz"), duijieQuery.getSjjhbz()));
            }

            if (StringUtil.isNotBlank(duijieQuery.getServiceCode())) {
                ps.add(criteriaBuilder.equal(root.get("methodNo"), duijieQuery.getServiceCode()));
            }

            if (StringUtil.isNotBlank(duijieQuery.getTaskId())) {
                ps.add(criteriaBuilder.equal(root.get("taskId"), duijieQuery.getTaskId()));
            }

            criteriaQuery.where(ps.toArray(new Predicate[0]));

            criteriaQuery.orderBy(criteriaBuilder.desc(root.get("id")));
            return criteriaQuery.getRestriction();
        }), pageable);

        return PageDTO.of(data.getTotalElements(), data.getTotalPages(), data.getContent());
    }

    public Object getExecutingDuijieBatchData(DuijieBaseDataQuery duijieQuery) {
        StringBuilder queryCountSQL = new StringBuilder("select COUNT(*) from DUIJIE_BATCH a, DUIJIE_ASYNC b where a.batchId=b.batchId and a.sjjhbz=3 ");
        Map<String, Object> params = new HashMap<>(2);
        if (StringUtil.isNotBlank(duijieQuery.getCreditCode())) {
            queryCountSQL.append(" and a.batchId like :creditCode");
            params.put("creditCode", "%" + duijieQuery.getCreditCode() + "%");
        }
        if (StringUtil.isNotBlank(duijieQuery.getServiceCode())) {
            queryCountSQL.append(" and methodNo=:methodNo");
            params.put("methodNo", duijieQuery.getServiceCode());
        }

        Long total = EntityManagerUtil.queryForCount(entityManager, queryCountSQL.toString(), params);
        int pageCount = total.intValue() / duijieQuery.getSize();
        String querySQL = StringUtil.replace(queryCountSQL.toString(), "COUNT(*)", "a.id as batchTableId,a.batchId,a.createDate,b.methodNo,b.type,b.taskId,b.sjjhbz");
        querySQL += " order by a.ztDataKey, a.createDate,batchOrder,b.id";
        List<Map<String, Object>> maps = EntityManagerUtil.queryForMapList(entityManager, querySQL, params, duijieQuery);
        return PageDTO.of(total, pageCount, maps);
    }

    /**
     * 按地区汇总数据
     *
     * @param duijieQuery
     * @return
     */
    public List<Map<String, Object>> getTreeStatisticsData(TreeStatisticsQuery duijieQuery) {
        // 四种资产的查询语句
        String assetSQL = "(\n" +
        "\t\tselect id,DATA_KEY,TYSHXYDM,GDZCLX_BM,BM,MC from DJ_GDZC union \n" +
                "\t\tselect id,DATA_KEY,TYSHXYDM,GDZCLX_BM,BM,NAME from DJ_CHZC union\n" +
                "\t\tselect id,DATA_KEY,TYSHXYDM,GDZCLX_BM,BM,NAME from DJ_SWZC union\n" +
                "\t\tselect id,DATA_KEY,TYSHXYDM,GDZCLX_BM,BM,NAME from DJ_JTTD\n" +
                "\t)";

        // 凭证条件
        String existsVoucherSQL = "exists (select 1 from DJ_PZM pz where ZT_DATA_KEY=A.DATA_KEY and pz.KJQJ like :yearsLike)";
        // 资产条件
        String existsAssetSQL = "exists (select 1 from  " + assetSQL + " zc where TYSHXYDM=A.TYSHXYDM )";
        // 合同
        String existsContractSQL = "exists (select 1 from DJ_HTXX ht where TYSHXYDM=A.TYSHXYDM)";
        // 无存款
        String noMoneySQL = "exists (select 1 from DJ_KMCSHSJ km where NF=:years and BM='102' and QCYE_JE=0 and ZT_DATA_KEY=A.DATA_KEY)";

        // 获取地区级别
        String distNo;
        if (StringUtil.isEmpty(duijieQuery.getDistNo())) {
            distNo = dataManager.getTopDistNo();
        } else {
            distNo = duijieQuery.getDistNo();
        }
        int distLength = distNo.length();
        int distLevel = distLength == 4 ? 6 : (distLength == 6 ? 9 : 12);
        String groupBySQL = distLevel < 12 ? "LEFT(A.distNo, " + distLevel + ")" : "A.TYSHXYDM";
        String distNameSQL = distLevel < 12 ? "(select top 1 distName from ${ZCDB}dist where distNo=" + groupBySQL + ")" : "max(A.mc)";
        String countSQL = "COUNT(*) cnt, SUM(case when A.ztType=1 then 1 else 0 end) n1_cnt, SUM(case when A.ztType=2 then 1 else 0 end) n2_cnt, SUM(case when A.ztType=3 then 1 else 0 end) n3_cnt";

        String querySQL = "with A as (select a.DATA_KEY,a.TYSHXYDM,a.mc,c.distNo,case when TYSHXYDM like 'N1%' then 1 when TYSHXYDM like 'N2%' then 2 when TYSHXYDM like 'N3%' then 3 else 9 end ztType from DJ_ZTXX a left join ${CWDB}acc_set b on a.TYSHXYDM=b.zucode\n" +
                "left join ${ZCDB}zt c on b.acc_set=c.BS_ztNo where a.TYSHXYDM like 'N%' and c.distNo like :distNo)\n" +
                "select aa.distNo,aa.distName," +
//                "isnull(aa.cnt,0) zt_cnt,isnull(aa.n1_cnt,0) zt_n1_cnt,isnull(aa.n2_cnt,0) zt_n2_cnt,isnull(aa.n3_cnt,0) zt_n3_cnt," +
                buildCountSql("aa", "zt") + ", \n" + buildCountSql("bb", "pz") + ", \n" +
                buildCountSql("cc", "zc") + ", \n" + buildCountSql("dd", "ht") + ", \n" +
                buildCountSql("ee", "no_pz") + ", \n" + buildCountSql("ff", "no_zc") + ", \n" +
                buildCountSql("gg", "no_ht") + ", \n" + buildCountSql("hh", "no_ck") + ", \n" +
                buildCountSql("ii", "no_4") + " \n" +
//                "isnull(bb.cnt,0) pz_cnt,isnull(bb.n1_cnt,0) pz_n1_cnt,isnull(bb.n2_cnt,0) pz_n2_cnt,isnull(bb.n3_cnt,0) pz_n3_cnt," +
//                "isnull(cc.cnt,0) zc_cnt,isnull(cc.n1_cnt,0) zc_n1_cnt,isnull(cc.n2_cnt,0) zc_n2_cnt,isnull(cc.n3_cnt,0) zc_n3_cnt," +
//                "isnull(dd.cnt,0) ht_cnt,isnull(dd.n1_cnt,0) ht_n1_cnt,isnull(dd.n2_cnt,0) ht_n2_cnt,isnull(dd.n3_cnt,0) ht_n3_cnt," +
//                "isnull(ee.cnt,0) no_pz_cnt,isnull(ee.n1_cnt,0) no_pz_n1_cnt,isnull(ee.n2_cnt,0) no_pz_n2_cnt,isnull(ee.n3_cnt,0) no_pz_n3_cnt," +
//                "isnull(ff.cnt,0) no_zc_cnt,isnull(ff.n1_cnt,0) no_zc_n1_cnt,isnull(ff.n2_cnt,0) no_zc_n2_cnt,isnull(ff.n3_cnt,0) no_zc_n3_cnt," +
//                "isnull(gg.cnt,0) no_ht_cnt,isnull(gg.n1_cnt,0) no_ht_n1_cnt,isnull(gg.n2_cnt,0) no_ht_n2_cnt,isnull(gg.n3_cnt,0) no_ht_n3_cnt," +
//                "isnull(hh.cnt,0) no_ck_cnt,isnull(hh.n1_cnt,0) no_ck_n1_cnt,isnull(hh.n2_cnt,0) no_ck_n2_cnt,isnull(hh.n3_cnt,0) no_ck_n3_cnt," +
//                "isnull(ii.cnt,0) no_4_cnt,isnull(ii.n1_cnt,0) no_4_n1_cnt,isnull(ii.n2_cnt,0) no_4_n2_cnt,isnull(ii.n3_cnt,0) no_4_n3_cnt" +


                " from (\n" +
                "--账套总数\n" +
                "\tselect " + groupBySQL + " distNo, " + distNameSQL + " distName, " + countSQL + " from A group by " + groupBySQL + "\n" +
                ") aa left join (\n" +
                "--凭证总数\n" +
                "\tselect " + groupBySQL + " distNo," + countSQL + " from A where " + existsVoucherSQL + " group by " + groupBySQL + "\n" +
                ") bb on aa.distNo=bb.distNo left join (\n" +
                "--资产数\n" +
                "\tselect " + groupBySQL + " distNo," + countSQL + " from A where " + existsAssetSQL + " group by " + groupBySQL + "\n" +
                "\t\t) cc on aa.distNo=cc.distNo left join (\n" +
                "--合同数\n" +
                "\tselect " + groupBySQL + " distNo," + countSQL + " from A where " + existsContractSQL + " group by " + groupBySQL + "\n" +
                ") dd on aa.distNo=dd.distNo left join (\n" +
                "--无凭证\n" +
                "\tselect " + groupBySQL + " distNo, " + countSQL + " from A where not " + existsVoucherSQL + " group by " + groupBySQL + "\n" +
                ") ee on aa.distNo=ee.distNo left join (\n" +
                "--无资产\n" +
                "\tselect " + groupBySQL + " distNo," + countSQL + " from A where not " + existsAssetSQL + " group by " + groupBySQL + "\n" +
                ") ff on aa.distNo=ff.distNo left join (\n" +
                "--无合同\n" +
                "\tselect " + groupBySQL + " distNo, " + countSQL + " from A where not " + existsContractSQL + " group by " + groupBySQL + "\n" +
                ") gg on aa.distNo=gg.distNo left join (\n" +
                "--无存款\n" +
                "\tselect " + groupBySQL + " distNo, " + countSQL + " from A where " + noMoneySQL + " group by " + groupBySQL + "\n" +
                ") hh on aa.distNo=hh.distNo left join (\n" +
                "--四无\n" +
                "\tselect " + groupBySQL + " distNo, " + countSQL + " from A where not " + existsVoucherSQL + " and not " + existsAssetSQL + " and not " + existsContractSQL + " and " + noMoneySQL +
                " group by " + groupBySQL + "\n" +
                ") ii on aa.distNo=ii.distNo";

        String cwdb = dataManager.getCwDBName(), zcdb = dataManager.getZcDBName();
        querySQL = StringUtil.replace(querySQL, "${CWDB}", cwdb);
        querySQL = StringUtil.replace(querySQL, "${ZCDB}", zcdb);

        Map<String, Object> params = new HashMap<>(3);
        params.put("years", duijieQuery.getYears());
        params.put("yearsLike", duijieQuery.getYears() + "%");
        params.put("distNo", distNo + "%");

//        String querySQL = StringUtil.replace(querySQL, "COUNT(*)", "");
        querySQL += " order by aa.distNo";
//        List<TreeStatisticsDataVO> maps = EntityManagerUtil.queryForClass(entityManager, querySQL, params, TreeStatisticsDataVO.class);
        // 合计行
//        TreeStatisticsDataVO sumMap = new TreeStatisticsDataVO();
//        sumMap.setDistNo(distNo);
//        sumMap.setDistName(dataManager.getDistNameByDistNo(distNo));
//        if (sumMap.getDistName() == null) sumMap.setDistName("合计");
//        for (TreeStatisticsDataVO vo : maps) {
//            sumMap.setZt_cnt(sumMap.getZt_cnt() + vo.getZt_cnt());
//            sumMap.setZt_n1_cnt(sumMap.getZt_n1_cnt() + vo.getZt_n1_cnt());
//            sumMap.setZt_n2_cnt(sumMap.getZt_n2_cnt() + vo.getZt_n2_cnt());
//            sumMap.setZt_n3_cnt(sumMap.getZt_n3_cnt() + vo.getZt_n3_cnt());
//            sumMap.setPz_cnt(sumMap.getPz_cnt() + vo.getPz_cnt());
//            sumMap.setPz_n1_cnt(sumMap.getPz_n1_cnt() + vo.getPz_n1_cnt());
//            sumMap.setPz_n2_cnt(sumMap.getPz_n2_cnt() + vo.getPz_n2_cnt());
//            sumMap.setPz_n3_cnt(sumMap.getPz_n3_cnt() + vo.getPz_n3_cnt());
//            sumMap.setZc_cnt(sumMap.getZc_cnt() + vo.getZc_cnt());
//            sumMap.setZc_n1_cnt(sumMap.getZc_n1_cnt() + vo.getZc_n1_cnt());
//            sumMap.setZc_n2_cnt(sumMap.getZc_n2_cnt() + vo.getZc_n2_cnt());
//            sumMap.setZc_n3_cnt(sumMap.getZc_n3_cnt() + vo.getZc_n3_cnt());
//            sumMap.setHt_cnt(sumMap.getHt_cnt() + vo.getHt_cnt());
//            sumMap.setHt_n1_cnt(sumMap.getHt_n1_cnt() + vo.getHt_n1_cnt());
//            sumMap.setHt_n2_cnt(sumMap.getHt_n2_cnt() + vo.getHt_n2_cnt());
//            sumMap.setHt_n3_cnt(sumMap.getHt_n3_cnt() + vo.getHt_n3_cnt());
//            sumMap.setNo_pz_cnt(sumMap.getNo_pz_cnt() + vo.getNo_pz_cnt());
//            sumMap.setNo_pz_n1_cnt(sumMap.getNo_pz_n1_cnt() + vo.getNo_pz_n1_cnt());
//            sumMap.setNo_pz_n2_cnt(sumMap.getNo_pz_n2_cnt() + vo.getNo_pz_n2_cnt());
//            sumMap.setNo_pz_n3_cnt(sumMap.getNo_pz_n3_cnt() + vo.getNo_pz_n3_cnt());
//            sumMap.setNo_zc_cnt(sumMap.getNo_zc_cnt() + vo.getNo_zc_cnt());
//            sumMap.setNo_zc_n1_cnt(sumMap.getNo_zc_n1_cnt() + vo.getNo_zc_n1_cnt());
//            sumMap.setNo_zc_n2_cnt(sumMap.getNo_zc_n2_cnt() + vo.getNo_zc_n2_cnt());
//            sumMap.setNo_zc_n3_cnt(sumMap.getNo_zc_n3_cnt() + vo.getNo_zc_n3_cnt());
//            sumMap.setNo_ht_cnt(sumMap.getNo_ht_cnt() + vo.getNo_ht_cnt());
//            sumMap.setNo_ht_n1_cnt(sumMap.getNo_ht_n1_cnt() + vo.getNo_ht_n1_cnt());
//            sumMap.setNo_ht_n2_cnt(sumMap.getNo_ht_n2_cnt() + vo.getNo_ht_n2_cnt());
//            sumMap.setNo_ht_n3_cnt(sumMap.getNo_ht_n3_cnt() + vo.getNo_ht_n3_cnt());
//            sumMap.setNo_ck_cnt(sumMap.getNo_ck_cnt() + vo.getNo_ck_cnt());
//            sumMap.setNo_ck_n1_cnt(sumMap.getNo_ck_n1_cnt() + vo.getNo_ck_n1_cnt());
//            sumMap.setNo_ck_n2_cnt(sumMap.getNo_ck_n2_cnt() + vo.getNo_ck_n2_cnt());
//            sumMap.setNo_ck_n3_cnt(sumMap.getNo_ck_n3_cnt() + vo.getNo_ck_n3_cnt());
//            sumMap.setNo_4_cnt(sumMap.getNo_4_cnt() + vo.getNo_4_cnt());
//            sumMap.setNo_4_n1_cnt(sumMap.getNo_4_n1_cnt() + vo.getNo_4_n1_cnt());
//            sumMap.setNo_4_n2_cnt(sumMap.getNo_4_n2_cnt() + vo.getNo_4_n2_cnt());
//            sumMap.setNo_4_n3_cnt(sumMap.getNo_4_n3_cnt() + vo.getNo_4_n3_cnt());
//        }
        List<Map<String, Object>> maps = EntityManagerUtil.queryForMapList(entityManager, querySQL, params);
        // 合计行
        Map<String, Object> sumMap = new HashMap<>();
        sumMap.put("distNo", distNo);
        sumMap.put("distName", (dataManager.getDistNameByDistNo(distNo)));
        sumMap.putIfAbsent("distName", "合计");
        for (Map<String, Object> map : maps) {
            for (String key : map.keySet()) {
                if (StringUtil.endsWithIgnoreCase(key, "_cnt")) {
                    sumMap.putIfAbsent(key, 0);
                    int sumValue = (Integer) sumMap.get(key) + (Integer) map.get(key);
                    sumMap.put(key, sumValue);
                }
            }
        }

        maps.add(0, sumMap);
        return maps;
    }

    private static String buildCountSql(String tableAlias, String fieldPrefix) {
        return "isnull(" + tableAlias + ".cnt,0) " + fieldPrefix + "_cnt,isnull(" + tableAlias + ".n1_cnt,0) " + fieldPrefix + "_n1_cnt," +
                "isnull(" + tableAlias + ".n2_cnt,0) " + fieldPrefix + "_n2_cnt,isnull(" + tableAlias + ".n3_cnt,0) " + fieldPrefix + "_n3_cnt";
    }

    private static void addHead(String fieldPrefix, List<List<String>> head) {
        head.add(Arrays.asList(fieldPrefix, "总计"));
        head.add(Arrays.asList(fieldPrefix, "联合总社"));
        head.add(Arrays.asList(fieldPrefix, "经联社"));
        head.add(Arrays.asList(fieldPrefix, "经济社"));
    }

    private static void addFieldNames(String fieldPrefix, List<String> fieldNames) {
        fieldNames.add(fieldPrefix + "_cnt");
        fieldNames.add(fieldPrefix + "_n3_cnt");
        fieldNames.add(fieldPrefix + "_n2_cnt");
        fieldNames.add(fieldPrefix + "_n1_cnt");
    }


    public List<Map<String, Object>> getDist(String distNo, Integer level) {
        List<Map<String, Object>> dists = dataManager.getDistsByDistNoAndLevel(distNo, level);
        return ListUtil.toSingleList(dists, dist -> {
            Map<String, Object> map = new HashMap<>();
            map.put("value", dist.get("distNo"));
            map.put("label", dist.get("distName"));
            map.put("leaf", level == 1); // 是否为叶子结点
            return map;
        });
    }

    /**
     * 导出
     */
    public void exportExcelOfTreeStatisticsData(TreeStatisticsQuery duijieQuery, HttpServletResponse response) throws IOException {
        List<Map<String, Object>> treeStatisticsData = getTreeStatisticsData(duijieQuery);
        Map<String, List<Map<String, Object>>> sheetsListData = Collections.singletonMap("Sheet1", treeStatisticsData);

        List<List<String>> eachHead = new ArrayList<>();
        Map<String, List<List<String>>> head = Collections.singletonMap("Sheet1", eachHead);
        List<String> eachFieldNames = new ArrayList<>();
        Map<String, List<String>> fieldNames = Collections.singletonMap("Sheet1", eachFieldNames);
        // 地区
        eachHead.add(Arrays.asList("地区名称", "地区名称"));
        eachFieldNames.add("distName");
        for (String fieldNamePrefix : Arrays.asList("建账套组织数","建凭证组织数","建资产组织数","建合同组织数","无凭证组织数","无资产组织数","无合同组织数","无存款组织数","四无组织数")) {
            addHead(fieldNamePrefix, eachHead);
        }
        for (String fieldPrefix : Arrays.asList("zt", "pz", "zc", "ht", "no_pz", "no_zc", "no_ht", "no_ck", "no_4")) {
            addFieldNames(fieldPrefix, eachFieldNames);
        }


        response.setCharacterEncoding("utf-8");
        try {
            // 导出的excel文件名
            String exportExcelFileName = StringUtil.replace("四无账套统计表.xlsx", " ", "");
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(exportExcelFileName, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setContentType("application/vnd.ms-excel;charset=gb2312");



        ExcelUtil.writeExcelToWeb(response.getOutputStream(), head, fieldNames, sheetsListData, Collections.emptyMap());
    }

    public Object getRemainData(DuijieBaseDataQuery query) {
        // 获取现在的会计区间
        YearMonth lastYearMonth = YearMonth.now().plusMonths(-1);
        String lastYearMonthString = CommonUtil.getSelectedYearMonth(lastYearMonth.getYear(), lastYearMonth.getMonthValue());
//        String queryCountSQL = "with A as (\n" +
//                "select isnull(b.acc_set, TYSHXYDM) as zt,SSQJ as SSQJ from DJ_ZCFZB a left join ${CWDB}acc_set b on a.TYSHXYDM=b.zucode where opType<>3 and sjjhbz=0 and SSQJ<>:lastYearMonthString and exists (select 1 from DJ_ZTXX where TYSHXYDM=a.TYSHXYDM)\n" +
//                ") \n" +
//                "\n" +
//                "select COUNT(*) from (\n" +
//                "select zt,stuff((select ','+ssqj from A a2 where a2.zt=A.zt for xml path('')),1,1,'') ssqj from A group by zt\n" +
//                ") aa";
//        if (EntityManagerUtil.IS_DM) {
//            queryCountSQL = "select COUNT(*) from (\n" +
//                    "select zt, wm_concat(SSQJ) as SSQJ from (\n" +
//                    "select isnull(b.acc_set, TYSHXYDM) as zt,SSQJ as SSQJ from DJ_ZCFZB a left join ${CWDB}acc_set b on a.TYSHXYDM=b.zucode where opType<>3 and sjjhbz=0 and SSQJ<>:lastYearMonthString and exists (select 1 from DJ_ZTXX where TYSHXYDM=a.TYSHXYDM)\n" +
//                    ")  aa group by zt\n" +
//                    ")  aaa ";
//        }
        String queryCountSQL = "select COUNT(*) from DJ_ZCFZB a left join ${CWDB}acc_set b on a.TYSHXYDM=b.zucode where opType<>3 and sjjhbz=0 and SSQJ<>:lastYearMonthString and exists (select 1 from DJ_ZTXX where TYSHXYDM=a.TYSHXYDM)";
        String cwdb = dataManager.getCwDBName();
        Map<String, Object> params = Collections.singletonMap("lastYearMonthString", lastYearMonthString);
        queryCountSQL = StringUtil.replace(queryCountSQL, "${CWDB}", cwdb);

        Long total = EntityManagerUtil.queryForCount(entityManager, queryCountSQL, params);
        int pageCount = total.intValue() / query.getSize();
//        String querySQL = StringUtil.replace(queryCountSQL, "COUNT(*)", "zt as zt, SSQJ as SSQJ");
//        querySQL += " order by zt";
        String querySQL = StringUtil.replace(queryCountSQL, "COUNT(*)", "isnull(b.acc_set, TYSHXYDM) as zt,SSQJ as SSQJ");
        querySQL += " order by SSQJ, zt";
        List<Map<String, Object>> maps = EntityManagerUtil.queryForMapList(entityManager, querySQL, params, query);
        return PageDTO.of(total, pageCount, maps);
    }
}
